// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

namespace SnapObjects.Data
{
    /// <summary>
    ///     Represents an object used to build a SQL SELECT statement.
    ///     The SQL SELECT statement can be used to retrieve rows from the database.
    /// </summary>
    public interface ISqlQueryBuilder : ISqlFromBuilder, ISqlBuilder
    {
        /// <summary>
        ///     Adds a column to the select list of a SELECT clause.
        /// </summary>
        /// <param name="column">The column name to be added to the select list of a SELECT clause.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Select(string column);

        /// <summary>
        ///     Adds a column to the select list of a SELECT clause, and specifies an alias for this column.
        /// </summary>
        /// <param name="column">The column name to be added to the select list of a SELECT clause.</param>
        /// <param name="alias">The alias of the column.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Select(string column, string alias);

        /// <summary>
        ///     Adds multiple columns to the select list of a SELECT clause.
        /// </summary>
        /// <param name="columns">An array of column names to be added to the select list of a SELECT clause.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Select(string[] columns);

        /// <summary>
        ///     Adds a raw SQL to the select list of a SELECT clause.
        /// </summary>
        /// <param name="selectRaw">
        ///     A string of raw SQL to be added to the SELECT clause.
        ///     Actually, you can write any raw SQL if the SQL statement is valid.
        /// </param>
        /// <param name="parameters"></param>
        /// <returns>
        ///     (Optional) An array of ISqlParameter objects which define the SQL parameters used in the raw SQL.
        /// </returns>
        ISqlQueryBuilder SelectRaw(string selectRaw, params ISqlParameter[] parameters);

        /// <summary>
        ///     Adds a raw SQL as a column to the select list of a SELECT clause, and specifies an alias for the raw SQL.
        /// </summary>
        /// <param name="selectRaw">
        ///     A string of raw SQL to be added to the SELECT clause.
        ///     It is usually a SQL subquery.
        /// </param>
        /// <param name="builderAlias">
        ///     The alias of the raw SQL. You can use the RemoveSelect method to remove this column by the alias of the raw SQL.
        /// </param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which define the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder SelectRaw(string selectRaw, string builderAlias, params ISqlParameter[] parameters);

        /// <summary>
        ///     Adds a column (which is returned by a subquery specified by an ISqlBuilder object) to the select list of a SELECT clause. 
        ///     It uses the name property of the ISqlBuilder object as the alias for this column by default.
        /// </summary>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Select(ISqlBuilder sqlBuilder);

        /// <summary>
        ///     Adds a column (which is returned by a subquery specified by an ISqlBuilder object) to the select list of a 
        ///     SELECT clause and specifies an alias for this column.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery.
        /// </param>
        /// <param name="alias">The alias for this column.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Select(ISqlBuilder sqlBuilder, string alias);

        /// <summary>
        ///     Replaces the select list in the SELECT clause with the specified raw SQL.
        /// </summary>
        /// <param name="selectRaw">The raw SQL which will replace the select list in the SELECT clause.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder ReplaceSelect(string selectRaw);

        /// <summary>
        ///     Removes a column (specified by alias) from a selected list.
        /// </summary>
        /// <param name="alias">The alias of a column.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder RemoveSelect(string alias);

        /// <summary>
        ///     Uses UNION to combine the result set of the current SQL SELECT statement with the result set of another 
        ///     SQL SELECT statement generated by the ISqlQueryBuilder object.
        /// </summary>
        /// <param name="queryBuilder">
        ///     The ISqlQueryBuilder object which represents a SQL query whose result set is to be 
        ///     combined with the result set of the current SQL query.
        /// </param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Union(ISqlQueryBuilder queryBuilder);

        /// <summary>
        ///     Uses UNION to combine the result set of the current SQL SELECT statement with the result set of another 
        ///     SQL SELECT statement generated by the ISqlQueryBuilder object; and specifies an alias for the ISqlQueryBuilder object.
        /// </summary>
        /// <param name="queryBuilder">
        ///     The ISqlQueryBuilder object which represents a SQL query whose result set is to be combined with the 
        ///     result set of the current SQL query.
        /// </param>
        /// <param name="builderAlias">
        ///     The alias of the ISqlQueryBuilder object.
        /// </param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Union(ISqlQueryBuilder queryBuilder, string builderAlias);

        /// <summary>
        ///     Uses UNION ALL to combine the result set of the current SQL SELECT statement and the result set of 
        ///     another SQL SELECT statement generated by the ISqlQueryBuilder object. The combined result set includes 
        ///     duplicate records.
        /// </summary>
        /// <param name="queryBuilder">
        ///     The ISqlQueryBuilder object which represents a SQL query whose result set is to be combined with 
        ///     the result set of the current SQL query.
        /// </param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder UnionAll(ISqlQueryBuilder queryBuilder);

        /// <summary>
        ///     Uses UNION ALL to combine the result set of the current SQL SELECT statement and the result set of another SQL 
        ///     SELECT statement generated by the ISqlQueryBuilder object (the combined result set includes duplicate records); 
        ///     and specifies an alias for the ISqlQueryBuilder object.
        /// </summary>
        /// <param name="queryBuilder">
        ///     The ISqlQueryBuilder object which represents a SQL query whose result set is to be combined with the current SQL query.
        /// </param>
        /// <param name="builderAlias">The alias of the ISqlQueryBuilder object.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder UnionAll(ISqlQueryBuilder queryBuilder, string builderAlias);

        /// <summary>
        ///     Uses UNION to combine the result set of the current SQL SELECT statement with the result set of 
        ///     another raw SQL SELECT statement.
        /// </summary>
        /// <param name="sqlRaw">
        ///     The raw SQL SELECT whose result set is to be combined with the result set of the current SQL query.
        /// </param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Union(string sqlRaw);

        /// <summary>
        ///     Uses UNION ALL to combine the result set of the current SQL SELECT statement with the result set 
        ///     of another raw SQL SELECT statement. The combined result set includes duplicate records.
        /// </summary>
        /// <param name="sqlRaw">
        ///     The raw SQL SELECT whose result set is to be combined with the result set of the current SQL query.
        /// </param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder UnionAll(string sqlRaw);

        /// <summary>
        ///     Uses UNION to combine the result set of the current SQL SELECT statement with the result set of 
        ///     another raw SQL SELECT statement; and specifies an alias for the raw SQL SELECT statement.
        /// </summary>
        /// <param name="sqlRaw">
        ///     The raw SQL SELECT whose result set is to be combined with the result set of the current SQL query.
        /// </param>
        /// <param name="builderAlias">The alias of the raw SQL SELECT statement.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder Union(string sqlRaw, string builderAlias);

        /// <summary>
        ///     Uses UNION ALL to combine the result set of the current SQL SELECT statement with the result set of 
        ///     another raw SQL SELECT statement (the combined result set includes duplicate records); and specifies
        ///     an alias for the raw SQL SELECT statement.
        /// </summary>
        /// <param name="sqlRaw">The raw SQL SELECT whose result set is to be combined with the current SQL query.</param>
        /// <param name="builderAlias">The alias of the raw SQL SELECT statement.</param>
        /// <returns>Returns the current ISqlQueryBuilder object.</returns>
        ISqlQueryBuilder UnionAll(string sqlRaw, string builderAlias);

        /// <summary>
        ///     Adds the DISTINCT keyword to the SELECT clause of the current object.
        ///     It specifies that only unique rows can appear in the result set.
        /// </summary>
        /// <returns>Returns an ISqlBuilder object which represents the current object.</returns>
        ISqlBuilder Distinct();

        /// <summary>
        ///     Adds the TOP keyword to the SELECT clause of the current object. It specifies that only a 
        ///     certain number of rows will be returned from the result set.
        /// </summary>
        /// <param name="count">Specifies the number of rows that will be returned from the result set.</param>
        /// <returns>
        ///     Returns an ISqlBuilder object which represents the current object.
        /// </returns>
        ISqlBuilder Top(int count);

        /// <summary>
        ///     Specifies that the SQL SELECT statement of the current object only returns the data on the specified
        ///     page of the paginated result set.
        /// </summary>
        /// <param name="page">The zero-based number of the page that you want to get the result set.</param>
        /// <param name="count">The number of rows for each page.</param>
        /// <returns>Returns an ISqlBuilder object which represents the current object.</returns>
        ISqlBuilder Page(int page, int count);
    }
}
